using System;
using System.Collections.Generic;
using System.Text;

public static class SQL
{

#region Kien
    public static readonly string Authenticate = @"
        select [UserName], [Password], [Role] from Accounts 
		where [UserName]=@UserName and [Password]=@Password and IsWorking=1";
    
    public static readonly string GetAccountRole = @"
        select [Role] from Accounts 
		where [UserName]=@UserName";

    public static readonly string GetCustomers = @"
        select CustomerID, ContactNumber, FullName, Address, Email, PhoneNumber 
        from Customers";

    public static readonly string GetCustomersByID = @"
        select CustomerID, ContactNumber, FullName, Address, Email, PhoneNumber 
        from Customers 
        where [CustomerID]=@CustomerID";

    public static readonly string GetCustomersByFullName = @"
        select CustomerID, ContactNumber, FullName, Address, Email, PhoneNumber 
        from Customers 
        where [FullName] like '%' + @FullName + '%'";

    public static readonly string InsertCustomer = @"
        insert into Customers(ContactNumber, FullName, Address, Email, PhoneNumber) 
        values(@ContactNumber, @FullName, @Address, @Email, @PhoneNumber)";

    public static readonly string UpdateCustomer = @"
        update Customers
        set ContactNumber=@ContactNumber, FullName=@FullName, Address=@Address, Email=@Email, PhoneNumber=@PhoneNumber
        where CustomerID=@CustomerID";

    public static readonly string DeleteCustomers = @"
        delete from Customers
        where CustomerID in ";

    public static readonly string GetDeliverables = @"
        select DeliverableID, [Type], 
            (select Services.ServiceName from Services 
                where Services.ServiceID=Deliverables.ServiceID) as Service, 
            Weight, 
            (select Distance.Description from Distance 
                where Deliverables.DistanceID=Distance.DistanceID) as Distance,
            CustomerID, [Status], ReceiverName, ReceiverAddress,
            (select Cities.CityName from Cities
                where Cities.CityID=Deliverables.ReceiverCityID) as City
        from Deliverables";

    public static readonly string GetDeliverablesByDeliverableID = @"
        select DeliverableID, [Type], 
            (select Services.ServiceName from Services 
                where Services.ServiceID=Deliverables.ServiceID) as Service, 
            Weight, 
            (select Distance.Description from Distance 
                where Deliverables.DistanceID=Distance.DistanceID) as Distance,
            CustomerID, [Status], ReceiverName, ReceiverAddress,
            (select Cities.CityName from Cities
                where Cities.CityID=Deliverables.ReceiverCityID) as City
        from Deliverables
        where DeliverableID=@DeliverableID";

    public static readonly string GetDeliverablesByCustomerID = @"
        select DeliverableID, [Type], 
            (select Services.ServiceName from Services 
                where Services.ServiceID=Deliverables.ServiceID) as Service, 
            Weight, 
            (select Distance.Description from Distance 
                where Deliverables.DistanceID=Distance.DistanceID) as Distance,
            CustomerID, [Status], ReceiverName, ReceiverAddress,
            (select Cities.CityName from Cities
                where Cities.CityID=Deliverables.ReceiverCityID) as City
        from Deliverables
        where CustomerID=@CustomerID";

    public static readonly string InsertDeliverable = @"spInsertDeliverable";

    public static readonly string UpdateDeliverable = @"
        update Deliverables
        set CustomerID=@CustomerID, [Type]=@Type, ServiceID=@ServiceID, 
            Weight=@Weight, DistanceID=@DistanceID, [Status]=@Status, 
            ReceiverName=@ReceiverName, ReceiverAddress=@ReceiverAddress, 
            ReceiverCityID=@ReceiverCityID
        where DeliverableID=@DeliverableID";

    public static readonly string DeleteDeliverables = @"
        delete from Deliverables
        where DeliverableID in ";

    public static readonly string InsertInvoice = @"
        insert into Invoices(DeliverableID, UserName, CreatedDate, Charge)
        values (@DeliverableID, @UserName, @CreatedDate, @Charge)";

    public static readonly string GetDistances = @"
        select DistanceID, Description from Distance";

    public static readonly string GetFaultDeliveries = @"
        select DeliverableID, FaultDate, Reason, RefundAmount, IsRepaid
        from FaultDelivery";

    public static readonly string GetFaultDeliveriesByID = @"
        select DeliverableID, FaultDate, Reason, RefundAmount, IsRepaid
        from FaultDelivery
        where DeliverableID=@DeliverableID";

    public static readonly string InsertFaultDelivery = @"
        insert into FaultDelivery
        values (@DeliverableID, @FaultDate, @Reason, @RefundAmount, @IsRepaid)";

    public static readonly string UpdateFaultDelivery = @"
        update FaultDelivery
        set FaultDate=@FaultDate, Reason=@Reason, 
            RefundAmount=@RefundAmount, IsRepaid=@IsRepaid
        where DeliverableID=@DeliverableID";

    public static readonly string DeleteFaultDeliveries = @"
        delete from FaultDelivery
        where DeliverableID in ";

    public static readonly string GetServicePriceByServiceID = @"
        SELECT  DISTINCT (SELECT w.WeightID FROM Weight w WHERE w.WeightID=s1.WeightID), 
		(SELECT w.Description FROM Weight w WHERE w.WeightID=s1.WeightID) AS Weight, 
		(SELECT Price FROM ServicePrice s2 WHERE s2.WeightID=s1.WeightID AND s2.DistanceID=1 AND s2.ServiceID=@ServiceID) AS [In the city],
		(SELECT Price FROM ServicePrice s3 WHERE s3.WeightID=s1.WeightID AND s3.DistanceID=2 AND s3.ServiceID=@ServiceID) AS [Interprovince < 300km],
		(SELECT Price FROM ServicePrice s4 WHERE s4.WeightID=s1.WeightID AND s4.DistanceID=3 AND s4.ServiceID=@ServiceID) AS [Interprovince > 300km]
FROM ServicePrice s1
WHERE s1.ServiceID=@ServiceID";

    public static readonly string UpdateServicePrice = @"
        UPDATE ServicePrice SET Price=@Price
        WHERE ServiceID=@ServiceID
	        AND DistanceID=@DistanceID
	        AND WeightID IN (SELECT WeightID 
					FROM Weight 
					WHERE Description=@WeightDescription)";

#endregion

#region An
    public static readonly string GetAllAccounts = @"
        Select UserName, FullName, Address, Email, PhoneNumber, Accounts.PinCode, 
        Branches.BranchName, IsWorking
        FROM Accounts JOIN Branches 
        ON Branches.PinCode = Accounts.PinCode
        WHERE Role = 'Employee'
        ";

    public static readonly string SearchAccountsByFullName = @"
        Select UserName, FullName, Address, Email, PhoneNumber, Accounts.PinCode, Branches.BranchName, IsWorking
        FROM Accounts JOIN Branches ON Branches.PinCode = Accounts.PinCode
        WHERE FullName LIKE '%' + @FullName + '%' AND Role = 'Employee'";

    public static readonly string SearchAccountsByBranchName = @"
        Select UserName, FullName, Address, Email, PhoneNumber, Accounts.PinCode, Branches.BranchName, IsWorking
        FROM Accounts JOIN Branches ON Branches.PinCode = Accounts.PinCode
        WHERE BranchName LIKE '%' + @BranchName + '%' AND Role = 'Employee'";

    public static readonly string InsertAccount = @"
        INSERT INTO Accounts(UserName, Password, FullName, Address, Email, PhoneNumber, [Role], PinCode)
        VALUES(@UserName, @Password, @FullName, @Address, @Email, @PhoneNumber, @Role, @PinCode)
        ";

    public static readonly string GetOneAccount = @"
       SELECT UserName, Password, FullName, Address, Email, PhoneNumber, PinCode, IsWorking 
       FROM Accounts WHERE UserName=@UserName
        ";
    public static readonly string UpdateAccount = @"
        UPDATE Accounts SET Password=@Password, Address=@Address, FullName=@FullName, Email=@Email,
	    PhoneNumber=@PhoneNumber, PinCode=@PinCode, IsWorking=@IsWorking
        WHERE UserName=@UserName
        ";

    public static readonly string UpdateAdmin = @"
        UPDATE Accounts SET FullName=@FullName, Address=@Address, Email=@Email, PhoneNumber=@PhoneNumber,PinCode=@PinCode
        WHERE UserName=@UserName AND [Role]='Admin'
        ";

    public static readonly string UpdateEmployeeProfile = @"
        EXEC spUpdateEmployeeProfile @UserName, @FullName, @Address, @Email, @PhoneNumber, @PinCode
        ";

    public static readonly string GetAccountsFromQueue = @"
       SELECT UserName, FullName, Address, Email, PhoneNumber, Branches.BranchName FROM AccountsQueue JOIN Branches ON
        Branches.PinCode = AccountsQueue.PinCode
        ";

    public static readonly string DeleteAccountsQueue = @"
        DELETE AccountsQueue
        ";

    public static readonly string GetAllVPPPayment = @"
        SELECT Deliverables.DeliverableID, Customers.FullName, VPPPayment.AmountToCollect, 
        VPPPayment.Charge, VPPPayment.IsCustomerPaid 
        FROM Deliverables INNER JOIN Customers ON Deliverables.CustomerID = Customers.CustomerID 
        INNER JOIN VPPPayment ON Deliverables.DeliverableID = VPPPayment.DeliverableID
        ";

    public static readonly string GetOneVPPPayment = @"
        SELECT DeliverableID, AmountToCollect, Charge, IsCustomerPaid FROM VPPPayment
        WHERE DeliverableID=@DeliverableID
        ";

    public static readonly string SearchVPPPaymentByDeliverableID = @"
        SELECT Deliverables.DeliverableID, Customers.FullName, VPPPayment.AmountToCollect, 
        VPPPayment.Charge, VPPPayment.IsCustomerPaid 
        FROM Deliverables INNER JOIN Customers ON Deliverables.CustomerID = Customers.CustomerID 
        INNER JOIN VPPPayment ON Deliverables.DeliverableID = VPPPayment.DeliverableID
        WHERE Deliverables.DeliverableID=@DeliverableID
        ";

    public static readonly string SearchVPPPaymentByFullName = @"
        SELECT Deliverables.DeliverableID, Customers.FullName, VPPPayment.AmountToCollect, 
        VPPPayment.Charge, VPPPayment.IsCustomerPaid 
        FROM Deliverables INNER JOIN Customers ON Deliverables.CustomerID = Customers.CustomerID 
        INNER JOIN VPPPayment ON Deliverables.DeliverableID = VPPPayment.DeliverableID
        WHERE FullName LIKE '%' + @FullName + '%'
        ";

    public static readonly string InsertVPPPayment = @"
        EXEC spInsertVPPPayment @DeliverableID, @AmountToCollect, @Charge, @IsCustomerPaid
        ";

    public static readonly string UpdateVPPPayment = @"
        UPDATE VPPPayment SET AmountToCollect=@AmountToCollect, Charge=@Charge, IsCustomerPaid=@IsCustomerPaid
        WHERE DeliverableID=@DeliverableID
        ";

    public static readonly string GetAllWeight = @"
        SELECT WeightID, FromWeight, ToWeight, WeightExtent, Description from Weight order by ToWeight ASC
        ";

    public static readonly string GetPriceByServiceAndDistance = @"
        SELECT WeightID, Price from ServicePrice WHERE ServiceID=@ServiceID AND DistanceID=@DistanceID
        ";
#endregion

#region Thinh

    public static readonly string getYearActiveCompany = @"SELECT DISTINCT(DATEPART(YEAR,CreatedDate)) FROM Invoices ";

    public static readonly string CheckDeliverableInStock = @"
             SELECT DeliverableID, StockPinCode, FromStock, ToStock, ReceivedDate, SentDate, Status  FROM Stock 
              WHERE DeliverableID=@DeliverableID AND StockPinCode=@StockPinCode";
    public static readonly string CheckDeliveryNumber = @"SELECT DeliverableID FROM Deliverables WHERE DeliverableID=@DeliverableID"; 

        public static readonly string BranchInformation=@" 
                SELECT PinCode,BranchName
                FROM Branches " ;

        public static readonly string SearchDeliverableOnDeliveryNumber = @" 
                    SELECT DeliverableID, StockPinCode, FromStock, ToStock, ReceivedDate, SentDate, Status
                    FROM Stock  WHERE DeliverableID like @DeliverableID AND StockPinCode=@StockPinCode";

        public static readonly string SearchDeliverableOnPinCode = @"
                    SELECT DeliverableID, StockPinCode, FromStock, ToStock, ReceivedDate, SentDate, Status
                    FROM Stock  WHERE StockPinCode=@StockPinCode AND (FromStock like @FromStock OR ToStock like @ToStock)";

        public static readonly string ListOfCity = @"
                SELECT CityID, CityName, Description
                FROM Cities";
        public static readonly string SearchDeliveryNumber = @"
                SELECT DeliverableID
                FROM Stock  WHERE DeliverableID like @DeliverableID AND StockPinCode=@StockPinCode";

        public static readonly string SearchFromStock = @"
                SELECT FromStock 
                FROM Stock  WHERE FromStock like @FromStock AND StockPinCode=@StockPinCode";

        public static readonly string SearchToStock = @"
                SELECT ToStock 
                FROM Stock  WHERE ToStock like @ToStock AND StockPinCode=@StockPinCode";

        public static readonly string ListStock = @"
                SELECT DeliverableID, StockPinCode, FromStock, ToStock, ReceivedDate, SentDate, Status
                FROM Stock WHERE StockPinCode=@StockPinCode";

        public static readonly string SelectedStock = @"
                SELECT DeliverableID, StockPinCode, FromStock, ToStock, ReceivedDate, SentDate, Status
                FROM Stock WHERE DeliverableID=@DeliverableID AND StockPinCode=@StockPinCode";

        public static readonly string DeleteSelectedDeliverable = @"
                DELETE FROM Stock WHERE DeliverableID=@DeliverableID AND StockPinCode=@StockPinCode ";

        public static readonly string AddStockWithFromStockToStock = @"
                INSERT INTO Stock (DeliverableID,StockPinCode,FromStock,ToStock,ReceivedDate,SentDate,Status)
                VALUES(@DeliverableID,@StockPinCode,@FromStock,@ToStock,@ReceivedDate,@SentDate,@Status)";

        public static readonly string AddStockWithFromStock = @"
                INSERT INTO Stock (DeliverableID,StockPinCode,FromStock,ReceivedDate,SentDate,Status)
                VALUES(@DeliverableID,@StockPinCode,@FromStock,@ReceivedDate,@SentDate,@Status)";

        public static readonly string AddStockWithoutSentDate = @"
               INSERT INTO Stock (DeliverableID,StockPinCode,FromStock,ToStock,ReceivedDate,Status)
                VALUES(@DeliverableID,@StockPinCode,@FromStock,@ToStock,@ReceivedDate,@Status)";

        public static readonly string AddStockWithToStock = @"
                INSERT INTO Stock(DeliverableID,StockPinCode,ToStock,ReceivedDate,SentDate,Status)
                VALUES(@DeliverableID,@StockPinCode,@ToStock,@ReceivedDate,@SentDate,@Status)";

        public static readonly string AddStock= @"
                INSERT INTO Stock (DeliverableID,StockPinCode,ReceivedDate,SentDate,Status)
                VALUES(@DeliverableID,@StockPinCode,@ReceivedDate,@SentDate,@Status)";
        public static readonly string ListService = @"
                SELECT ServiceID, ServiceName, ServiceImage, Description
                FROM Services";

        
        public static readonly string SelectedService = @"
                SELECT ServiceID, ServiceName, ServiceImage, Description
                FROM Services WHERE ServiceID=@ServiceID";

        public static readonly string AddService = @"
                INSERT INTO Services 
                VALUES(@ServiceName, @ServiceImage, @Description)";

        public static readonly string DeleteService = @"
                DELETE FROM Services WHERE ServiceID=@ServiceID";

        public static readonly string UpdateService = @"
                UPDATE Services SET 
                                        ServiceName=@ServiceName, 
                                        ServiceImage=@ServiceImage,
                                        Description=@Description 
                WHERE ServiceID=@ServiceID";

        public static readonly string ViewBranches = @"
                SELECT PinCode, BranchAddress, BranchName, BranchFax, BranchPhone, CityID
                FROM Branches ";

        public static readonly string SelectedBranch=@"
                SELECT PinCode, BranchAddress, BranchName, BranchFax, BranchPhone, CityID
                FROM Branches WHERE PinCode=@PinCode";

        public static readonly string AddBranch = @"
        INSERT INTO Branches VALUES( @PinCode, @BranchAddress, @BranchName, @BranchFax, @BranchPhone, @CityID)";

        public static readonly string EditBranch = @"
            UPDATE Branches SET BranchAddress=@BranchAddress, BranchName=@BranchName, BranchFax=@BranchFax, BranchPhone=@BranchPhone, CityID=@CityID
            WHERE  PinCode=@PinCode";

        public static readonly string DeleteBranch = @"
            DELETE FROM Branches WHERE Branches.PinCode=@PinCode
            ";
    #endregion

#region Khoa
    public static readonly string GetCustomerID = @"
        SELECT CustomerID FROM Deliverables WHERE DeliverableID = @DeliverableID";

    public static readonly string GetCustomerContactNumber = @"
        SELECT ContactNumber FROM Customers WHERE CustomerID = @CustomerID";

    public static readonly string GetDeliverableStatus = @"
        SELECT Type, (SELECT ServiceName FROM Services WHERE ServiceID = Deliverables.ServiceID) AS Service,
        (SELECT FullName FROM Customers WHERE CustomerID = Deliverables.CustomerID) AS CustomerName, Status, 
        ReceiverName, ReceiverAddress FROM Deliverables WHERE DeliverableID = @DeliverableID";

    public static readonly string GetPassword = @"
        SELECT Password FROM Accounts WHERE UserName = @UserName";

    public static readonly string UpdatePassword = @"
        UPDATE Accounts SET Password = @Password WHERE UserName = @UserName";
#endregion
}
